package zy.dao.stock.allocate.impl;

import java.util.Arrays;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.stock.allocate.StockAllocateReportDAO;
import zy.dto.stock.allocate.StockAllocateDetailReportDto;
import zy.dto.stock.allocate.StockAllocateReportDto;
import zy.util.CommonUtil;
import zy.util.StringUtil;

@Repository
public class StockAllocateReportDAOImpl extends BaseDaoImpl implements StockAllocateReportDAO{

	private String getAllocateReportSQL(Map<String, Object> params){
		StringBuffer sql = new StringBuffer();
		String bd_code = StringUtil.trimString(params.get("bd_code"));
		String tp_code = StringUtil.trimString(params.get("tp_code"));
		if(StringUtil.isNotEmpty(bd_code)){
			params.put("bdCodes", Arrays.asList(bd_code.split(",")));
			sql.append(" AND pd_bd_code IN(:bdCodes)");
		}
		if(StringUtil.isNotEmpty(tp_code)){
			params.put("tpCodes", Arrays.asList(tp_code.split(",")));
			sql.append(" AND pd_tp_code IN(:tpCodes)");
		}
		if(StringUtil.isNotEmpty(params.get("dp_code"))){
			sql.append(" AND dp_code = :dp_code ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_season"))){
			sql.append(" AND pd_season = :pd_season ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_year"))){
			sql.append(" AND pd_year = :pd_year ");
		}
		if(StringUtil.isNotEmpty(params.get("ac_manager"))){
			sql.append(" AND ac_manager = :ac_manager ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_code"))){
			sql.append(" AND pd_code = :pd_code ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_no"))){
			sql.append(" AND INSTR(pd_no,:pd_no) > 0 ");
		}
		if(StringUtil.isNotEmpty(params.get("begindate"))){
			sql.append(" AND ac_ar_date >= :begindate ");
		}
		if(StringUtil.isNotEmpty(params.get("enddate"))){
			sql.append(" AND ac_ar_date <= :enddate ");
		}
		sql.append(" AND ac_ar_state = 1");
		sql.append(" AND t.companyid = :companyid");
		return sql.toString();
	}
	
	@Override
	public Map<String, Object> countsumAllocateReport(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append(" COUNT(1) AS totalCount,");
		sql.append(" SUM(in_amount) AS in_amount,");
		sql.append(" SUM(out_amount) AS out_amount,");
		sql.append(" SUM(in_amount*acl_unitprice) AS in_money,");
		sql.append(" SUM(out_amount*acl_unitprice) AS out_money");
		
		sql.append(" FROM");
		sql.append(" (");
		sql.append(" SELECT dp_code,dp_name,acl_amount AS in_amount,0 AS out_amount,acl_unitprice,");
		sql.append(" pd_code,pd_no,pd_name,pd_bd_code,pd_tp_code,pd_season,t.companyid");
		sql.append(" FROM t_stock_allocate t");
		sql.append(" JOIN t_stock_allocatelist acl ON acl_number = ac_number AND acl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = acl_pd_code AND pd.companyid = acl.companyid");
		sql.append(" JOIN t_base_depot dp ON dp_code = ac_indp_code AND dp.companyid = t.companyid");
		sql.append(" JOIN t_base_shop sp ON sp_code = dp_shop_code AND sp.companyid = dp.companyid");
		sql.append(" WHERE 1=1");
		sql.append(getAllocateReportSQL(params));
		sql.append(" UNION ALL ");
		sql.append(" SELECT dp_code,dp_name,0 AS in_amount,acl_amount AS out_amount,acl_unitprice,");
		sql.append(" pd_code,pd_no,pd_name,pd_bd_code,pd_tp_code,pd_season,t.companyid");
		sql.append(" FROM t_stock_allocate t");
		sql.append(" JOIN t_stock_allocatelist acl ON acl_number = ac_number AND acl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = acl_pd_code AND pd.companyid = acl.companyid");
		sql.append(" JOIN t_base_depot dp ON dp_code = ac_outdp_code AND dp.companyid = t.companyid");
		sql.append(" JOIN t_base_shop sp ON sp_code = dp_shop_code AND sp.companyid = dp.companyid");
		sql.append(" WHERE 1=1");
		sql.append(getAllocateReportSQL(params));
		sql.append(" )temp");
		return namedParameterJdbcTemplate.queryForMap(sql.toString(), params);
	}

	@Override
	public List<StockAllocateReportDto> listAllocateReport(Map<String, Object> params) {
		String type = StringUtil.trimString(params.get("type"));
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append(" dp_code,dp_name,");
		sql.append(" SUM(in_amount) AS in_amount,");
		sql.append(" SUM(out_amount) AS out_amount,");
		sql.append(" SUM(in_amount*acl_unitprice) AS in_money,");
		sql.append(" SUM(out_amount*acl_unitprice) AS out_money");
		if("depot".equals(type)){
			sql.append(",dp_code AS id");
		}else if("brand".equals(type)){
			sql.append(" ,pd_bd_code AS code");
			sql.append(" ,(SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = temp.companyid LIMIT 1) AS name");
			sql.append(" ,CONCAT(dp_code,'_',pd_bd_code) AS id");
		}else if("type".equals(type)){
			sql.append(" ,pd_tp_code AS code");
			sql.append(" ,(SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = temp.companyid LIMIT 1) AS name");
			sql.append(" ,CONCAT(dp_code,'_',pd_tp_code) AS id");
		}else if("product".equals(type)){
			sql.append(" ,pd_code AS code,pd_name AS name,pd_no");
			sql.append(" ,CONCAT(dp_code,'_',pd_code) AS id");
		}else if("season".equals(type)){
			sql.append(" ,pd_season AS code,pd_season AS name");
			sql.append(" ,CONCAT(dp_code,'_',IFNULL(pd_season,'')) AS id");
		}
		sql.append(" FROM");
		sql.append(" (");
		sql.append(" SELECT dp_code,dp_name,acl_amount AS in_amount,0 AS out_amount,acl_unitprice,");
		sql.append(" pd_code,pd_no,pd_name,pd_bd_code,pd_tp_code,pd_season,t.companyid");
		sql.append(" FROM t_stock_allocate t");
		sql.append(" JOIN t_stock_allocatelist acl ON acl_number = ac_number AND acl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = acl_pd_code AND pd.companyid = acl.companyid");
		sql.append(" JOIN t_base_depot dp ON dp_code = ac_indp_code AND dp.companyid = t.companyid");
		sql.append(" JOIN t_base_shop sp ON sp_code = dp_shop_code AND sp.companyid = dp.companyid");
		sql.append(" WHERE 1=1");
		sql.append(getAllocateReportSQL(params));
		sql.append(" UNION ALL ");
		sql.append(" SELECT dp_code,dp_name,0 AS in_amount,acl_amount AS out_amount,acl_unitprice,");
		sql.append(" pd_code,pd_no,pd_name,pd_bd_code,pd_tp_code,pd_season,t.companyid");
		sql.append(" FROM t_stock_allocate t");
		sql.append(" JOIN t_stock_allocatelist acl ON acl_number = ac_number AND acl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = acl_pd_code AND pd.companyid = acl.companyid");
		sql.append(" JOIN t_base_depot dp ON dp_code = ac_outdp_code AND dp.companyid = t.companyid");
		sql.append(" JOIN t_base_shop sp ON sp_code = dp_shop_code AND sp.companyid = dp.companyid");
		sql.append(" WHERE 1=1");
		sql.append(getAllocateReportSQL(params));
		sql.append(" )temp");
		sql.append(" GROUP BY dp_code");
		if("depot".equals(type)){
		}else if("brand".equals(type)){
			sql.append(" ,pd_bd_code");
		}else if("type".equals(type)){
			sql.append(" ,pd_tp_code");
		}else if("product".equals(type)){
			sql.append(" ,pd_code");
		}else if("season".equals(type)){
			sql.append(" ,pd_season");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(StockAllocateReportDto.class));
	}

	private String getAllocateDetailReportSQL(Map<String, Object> params){
		StringBuffer sql = new StringBuffer();
		String bd_code = StringUtil.trimString(params.get("bd_code"));
		String tp_code = StringUtil.trimString(params.get("tp_code"));
		if(StringUtil.isNotEmpty(bd_code)){
			params.put("bdCodes", Arrays.asList(bd_code.split(",")));
			sql.append(" AND pd_bd_code IN(:bdCodes)");
		}
		if(StringUtil.isNotEmpty(tp_code)){
			params.put("tpCodes", Arrays.asList(tp_code.split(",")));
			sql.append(" AND pd_tp_code IN(:tpCodes)");
		}
		if(StringUtil.isNotEmpty(params.get("ac_outdp_code"))){
			sql.append(" AND ac_outdp_code = :ac_outdp_code ");
		}
		if(StringUtil.isNotEmpty(params.get("ac_indp_code"))){
			sql.append(" AND ac_indp_code = :ac_indp_code ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_season"))){
			sql.append(" AND pd_season = :pd_season ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_year"))){
			sql.append(" AND pd_year = :pd_year ");
		}
		if(StringUtil.isNotEmpty(params.get("ac_manager"))){
			sql.append(" AND ac_manager = :ac_manager ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_code"))){
			sql.append(" AND pd_code = :pd_code ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_no"))){
			sql.append(" AND INSTR(pd_no,:pd_no) > 0 ");
		}
		if(StringUtil.isNotEmpty(params.get("begindate"))){
			sql.append(" AND ac_ar_date >= :begindate ");
		}
		if(StringUtil.isNotEmpty(params.get("enddate"))){
			sql.append(" AND ac_ar_date <= :enddate ");
		}
		sql.append(" AND ac_ar_state = 1");
		sql.append(" AND t.companyid = :companyid");
		return sql.toString();
	}
	
	
	@Override
	public Map<String, Object> countsumAllocateDetailReport(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append(" COUNT(1) AS totalCount,");
		sql.append(" SUM(acl_amount) AS acl_amount,");
		sql.append(" SUM(acl_amount*acl_unitprice) AS acl_unitmoney");
		sql.append(" FROM t_stock_allocate t");
		sql.append(" JOIN t_stock_allocatelist acl ON acl_number = ac_number AND acl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = acl_pd_code AND pd.companyid = acl.companyid");
		sql.append(" JOIN t_base_depot dp ON dp_code = ac_indp_code AND dp.companyid = t.companyid");
		sql.append(" JOIN t_base_shop sp ON sp_code = dp_shop_code AND sp.companyid = dp.companyid");
		sql.append(" WHERE 1=1");
		sql.append(getAllocateDetailReportSQL(params));
		return namedParameterJdbcTemplate.queryForMap(sql.toString(), params);
	}

	@Override
	public List<StockAllocateDetailReportDto> listAllocateDetailReport(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT acl_id,ac_date,acl_number,ac_manager,acl_pd_code,acl_sub_code,acl_szg_code,acl_sz_code,acl_cr_code,acl_br_code,");
		sql.append(" acl_amount,acl_unitprice,t.companyid,pd_no,pd_name,pd_unit,pd_season,pd_year,");
		sql.append(" (SELECT dp_name FROM t_base_depot dp WHERE dp_code = ac_outdp_code AND dp.companyid = t.companyid LIMIT 1) AS outdepot_name,");
		sql.append(" (SELECT dp_name FROM t_base_depot dp WHERE dp_code = ac_indp_code AND dp.companyid = t.companyid LIMIT 1) AS indepot_name,");
		sql.append(" (SELECT cr_name FROM t_base_color cr WHERE cr_code = acl_cr_code AND cr.companyid = t.companyid LIMIT 1) AS cr_name,");
		sql.append(" (SELECT sz_name FROM t_base_size sz WHERE sz_code = acl_sz_code AND sz.companyid = t.companyid LIMIT 1) AS sz_name,");
		sql.append(" (SELECT br_name FROM t_base_bra br WHERE br_code = acl_br_code AND br.companyid = t.companyid LIMIT 1) AS br_name,");
		sql.append(" (SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = t.companyid LIMIT 1) AS bd_name,");
		sql.append(" (SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = t.companyid LIMIT 1) AS tp_name");
		sql.append(" FROM t_stock_allocate t");
		sql.append(" JOIN t_stock_allocatelist acl ON acl_number = ac_number AND acl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = acl_pd_code AND pd.companyid = acl.companyid");
		sql.append(" WHERE 1=1");
		sql.append(getAllocateDetailReportSQL(params));
		if(StringUtil.isNotEmpty(params.get(CommonUtil.SIDX))){
			sql.append(" ORDER BY ").append(params.get(CommonUtil.SIDX)).append(" ").append(params.get(CommonUtil.SORD));
		}else {
			sql.append(" ORDER BY acl_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(StockAllocateDetailReportDto.class));
	}

}
